﻿using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;
using System;

namespace Shuba.Data
{
	public class BranchTarget
	{
        public BranchTarget()
		{
		}

        public List<BranchTargetInfo> GetList(string BranchID, string TargetYear)
        {
            List<BranchTargetInfo> list = new List<BranchTargetInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT 
	                                                    A.MONTH_ID, 
	                                                    A.MONTH_NAME, 
	                                                    ISNULL(B.TARGET_AMOUNT,0) AS TARGET_AMOUNT, 
	                                                    ISNULL(B.NOA,0) AS NOA,
	                                                    ISNULL(B.TARGET_AMOUNT_REAL,0) AS TARGET_AMOUNT_REAL, 
	                                                    ISNULL(B.NOA_REAL,0) AS NOA_REAL
                                                    FROM 
                                                    (
	                                                    SELECT 1 AS MONTH_ID, DATENAME(MONTH, '01-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 2 AS MONTH_ID, DATENAME(MONTH, '02-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 3 AS MONTH_ID, DATENAME(MONTH, '03-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 4 AS MONTH_ID, DATENAME(MONTH, '04-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 5 AS MONTH_ID, DATENAME(MONTH, '05-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 6 AS MONTH_ID, DATENAME(MONTH, '06-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 7 AS MONTH_ID, DATENAME(MONTH, '07-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 8 AS MONTH_ID, DATENAME(MONTH, '08-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 9 AS MONTH_ID, DATENAME(MONTH, '09-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 10 AS MONTH_ID, DATENAME(MONTH, '10-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 11 AS MONTH_ID, DATENAME(MONTH, '11-01-2010' ) AS MONTH_NAME
	                                                    UNION
	                                                    SELECT 12 AS MONTH_ID, DATENAME(MONTH, '12-01-2010' ) AS MONTH_NAME
                                                    ) A LEFT OUTER JOIN 
                                                    (
	                                                    SELECT TARGET_MONTH, TARGET_AMOUNT, NOA, TARGET_AMOUNT_REAL, NOA_REAL FROM TBL_BRANCH_TARGET_PLAN B
	                                                    WHERE
		                                                    B.BRANCH_ID = '' + @BRANCH_ID + ''
		                                                    AND B.TARGET_YEAR = '' + @TARGET_YEAR + ''
                                                    ) B ON A.MONTH_ID = B.TARGET_MONTH
                                                    ");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchID);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchTargetInfo(dr.GetInt32(0).ToString(), dr.GetString(1), dr.GetInt64(2), dr.GetInt32(3), dr.GetInt64(4), dr.GetInt32(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<BranchTargetInfo> GetListByCluster(string ClusterID, string TargetYear)
		{
            List<BranchTargetInfo> list = new List<BranchTargetInfo>();
            //Database db = DatabaseFactory.CreateDatabase();
			Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                            SELECT
                                                @TARGET_YEAR AS TARGET_YEAR,
                                                B.BRANCH_ID, 
	                                            B.BRANCH_NAME,
	                                            ISNULL(X.TARGET_AMOUNT,0) AS TARGET_AMOUNT,
	                                            ISNULL(X.NOA,0) AS NOA,
	                                            ISNULL(X.TARGET_AMOUNT_REAL,0) AS TARGET_AMOUNT_REAL,
	                                            ISNULL(X.NOA_REAL,0) AS NOA_REAL
                                            FROM TBL_MS_CLUSTER_BRANCH AS A INNER JOIN TBL_MS_BRANCH AS B ON A.BRANCH_ID = B.BRANCH_ID
                                            LEFT OUTER JOIN
                                            (
                                            SELECT
	                                            C.BRANCH_ID, 
	                                            ISNULL(SUM(C.TARGET_AMOUNT), 0) AS TARGET_AMOUNT, 
	                                            ISNULL(SUM(C.NOA), 0) AS NOA,
	                                            ISNULL(SUM(C.TARGET_AMOUNT_REAL), 0) AS TARGET_AMOUNT_REAL, 
	                                            ISNULL(SUM(C.NOA_REAL), 0) AS NOA_REAL
                                            FROM TBL_MS_CLUSTER_BRANCH AS A INNER JOIN TBL_MS_BRANCH AS B 
                                            ON A.BRANCH_ID = B.BRANCH_ID LEFT OUTER JOIN 
                                            TBL_BRANCH_TARGET_PLAN AS C ON B.BRANCH_ID = C.BRANCH_ID
                                            WHERE C.TARGET_YEAR = @TARGET_YEAR
                                            GROUP BY C.BRANCH_ID
                                            ) X ON B.BRANCH_ID = X.BRANCH_ID
                                            WHERE 
                                                A.CLUSTER_ID = @CLUSTER_ID
                                            ");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterID);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
			using (IDataReader dr = db.ExecuteReader(cmd))
			{
				while (dr.Read())
				{
                    list.Add(new BranchTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6)));
				}
                dr.Close();
			}
			return list;
		}

        public List<BranchTargetInfo> GetListApproval(string TargetYear, string UpdateBy, string CostCenter)
        {
            List<BranchTargetInfo> list = new List<BranchTargetInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT 
            		                    A.BRANCH_ID, 
            		                    A.BRANCH_NAME,
            		                    B.TARGET_YEAR,
                                        B.TARGET_AMOUNT,
            		                    B.NOA,
                                        B.TARGET_AMOUNT_REAL,
            		                    B.NOA_REAL,
            		                    B.ENTRY_BY,
            		                    B.ENTRY_DATE,
            		                    B.UPDATE_BY,
            		                    B.UPDATE_DATE,
                                        B.ACT_TYPE,
                                        B.TARGET_MONTH
                                FROM TBL_MS_BRANCH A INNER JOIN TBL_BRANCH_TARGET_PLAN_TMP B
                                ON A.BRANCH_ID = B.BRANCH_ID INNER JOIN
                                (
                                    SELECT A.BRANCH_ID
                                    FROM TBL_MS_CLUSTER_BRANCH AS A INNER JOIN
                                    TBL_MS_REGION_CLUSTER AS B ON A.CLUSTER_ID = B.CLUSTER_ID LEFT JOIN
                                    TBL_MS_HO_REGION AS C ON B.REGIONAL_ID = C.REGIONAL_ID
                                    WHERE (C.HO_ID = @COST_CENTER) OR (B.REGIONAL_ID = @COST_CENTER) OR (A.CLUSTER_ID = @COST_CENTER) OR (A.BRANCH_ID = @COST_CENTER)
                                    GROUP BY A.BRANCH_ID
                                ) X ON A.BRANCH_ID = X.BRANCH_ID
                                WHERE 
                                     (TARGET_YEAR = '' + @TARGET_YEAR + '')
                                     AND (RTRIM(B.UPDATE_BY) <> '' + @UPDATE_BY + '')
                                ");
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            db.AddInParameter(cmd, "@COST_CENTER", DbType.String, CostCenter);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12)));
                }
                dr.Close();
            }
            return list;
        }

        public List<BranchTargetInfo> GetListApprovalByMonth(string TargetYear, string TargetMonth, string UpdateBy)
        {
            List<BranchTargetInfo> list = new List<BranchTargetInfo>();
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT 
            		                    A.BRANCH_ID, 
            		                    A.BRANCH_NAME,
            		                    B.TARGET_YEAR,
                                        B.TARGET_AMOUNT,
            		                    B.NOA,
                                        B.TARGET_AMOUNT_REAL,
            		                    B.NOA_REAL,
            		                    B.ENTRY_BY,
            		                    B.ENTRY_DATE,
            		                    B.UPDATE_BY,
            		                    B.UPDATE_DATE,
                                        B.ACT_TYPE,
                                        B.TARGET_MONTH
                                FROM TBL_MS_BRANCH A INNER JOIN TBL_BRANCH_TARGET_PLAN_TMP B
                                ON A.BRANCH_ID = B.BRANCH_ID
                                WHERE 
                                     (TARGET_YEAR = '' + @TARGET_YEAR + '')
                                     AND (TARGET_MONTH = '' + @TARGET_MONTH + '') 
                                     AND (RTRIM(B.UPDATE_BY) <> '' + @UPDATE_BY + '')
                                ");
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            db.AddInParameter(cmd, "@TARGET_MONTH", DbType.String, TargetMonth);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new BranchTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11)));
                }
                dr.Close();
            }
            return list;
        }

        public BranchTargetInfo Get(string id, string TargetYear)
        {
            BranchTargetInfo bi = new BranchTargetInfo();
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT A.BRANCH_ID, B.BRANCH_NAME, A.TARGET_YEAR, A.TARGET_AMOUNT, A.NOA, A.TARGET_AMOUNT_REAL, A.NOA_REAL, A.ENTRY_BY, A.ENTRY_DATE, A.UPDATE_BY, A.UPDATE_DATE, A.TARGET_MONTH 
                                FROM TBL_BRANCH_TARGET_PLAN A INNER JOIN TBL_MS_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID WHERE (A.BRANCH_ID = '' + @BRANCH_ID + '') AND (A.TARGET_YEAR = '' + @TARGET_YEAR + '') 
                                ");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, id);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new BranchTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11));
                }
                dr.Close();
            }

            return bi;
        }

        public BranchTargetInfo Get(string id, string TargetYear, string TargetMonth)
        {
            BranchTargetInfo bi = new BranchTargetInfo();
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT A.BRANCH_ID, B.BRANCH_NAME, A.TARGET_YEAR, A.TARGET_AMOUNT, A.NOA, A.TARGET_AMOUNT_REAL, A.NOA_REAL, A.ENTRY_BY, A.ENTRY_DATE, A.UPDATE_BY, A.UPDATE_DATE, A.TARGET_MONTH 
                                FROM TBL_BRANCH_TARGET_PLAN A INNER JOIN TBL_MS_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID WHERE (A.BRANCH_ID = '' + @BRANCH_ID + '') AND (A.TARGET_YEAR = '' + @TARGET_YEAR + '') AND (A.TARGET_MONTH = '' + @TARGET_MONTH + '') 
                                ");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, id);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            db.AddInParameter(cmd, "@TARGET_MONTH", DbType.String, TargetMonth);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new BranchTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11));
                }
                dr.Close();
            }

            return bi;
        }

        public BranchTargetInfo GetApprovalByID(string id, string TargetYear)
        {
            BranchTargetInfo bi = new BranchTargetInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT 
            		                    A.BRANCH_ID, 
            		                    A.BRANCH_NAME,
            		                    B.TARGET_YEAR,
                                        B.TARGET_AMOUNT,
            		                    B.NOA,
                                        B.TARGET_AMOUNT_REAL,
            		                    B.NOA_REAL,
            		                    B.ENTRY_BY,
            		                    B.ENTRY_DATE,
            		                    B.UPDATE_BY,
            		                    B.UPDATE_DATE,
                                        B.ACT_TYPE,
                                        B.TARGET_MONTH
                                FROM TBL_MS_BRANCH A INNER JOIN TBL_BRANCH_TARGET_PLAN_TMP B
                                ON A.BRANCH_ID = B.BRANCH_ID
                                WHERE (B.BRANCH_ID = '' + @BRANCH_ID + '') AND (B.TARGET_YEAR = '' + @TARGET_YEAR + '') 
                                ");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, id);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new BranchTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12));
                }
                dr.Close();
            }

            return bi;
        }

        public BranchTargetInfo GetApprovalByID(string id, string TargetYear, string TargetMonth)
        {
            BranchTargetInfo bi = new BranchTargetInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT 
            		                    A.BRANCH_ID, 
            		                    A.BRANCH_NAME,
            		                    B.TARGET_YEAR,
                                        B.TARGET_AMOUNT,
            		                    B.NOA,
                                        B.TARGET_AMOUNT_REAL,
            		                    B.NOA_REAL,
            		                    B.ENTRY_BY,
            		                    B.ENTRY_DATE,
            		                    B.UPDATE_BY,
            		                    B.UPDATE_DATE,
                                        B.ACT_TYPE,
                                        B.TARGET_MONTH
                                FROM TBL_MS_BRANCH A INNER JOIN TBL_BRANCH_TARGET_PLAN_TMP B
                                ON A.BRANCH_ID = B.BRANCH_ID
                                WHERE (B.BRANCH_ID = '' + @BRANCH_ID + '') AND (B.TARGET_YEAR = '' + @TARGET_YEAR + '') AND (B.TARGET_MONTH = '' + @TARGET_MONTH + '')
                                ");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, id);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            db.AddInParameter(cmd, "@TARGET_MONTH", DbType.String, TargetMonth);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new BranchTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12));
                }
                dr.Close();
            }

            return bi;
        }

        public void Save(BranchTargetInfo BranchTarget)
        {
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
            	            UPDATE TBL_BRANCH_TARGET_PLAN_TMP SET
            		            TARGET_AMOUNT = @TARGET_AMOUNT, 
            		            NOA = @NOA, 
            		            TARGET_AMOUNT_REAL = @TARGET_AMOUNT_REAL, 
            		            NOA_REAL = @NOA_REAL, 
                                UPDATE_BY = @UPDATE_BY, 
                                UPDATE_DATE = @UPDATE_DATE, 
                                ACT_TYPE = @ACT_TYPE 
            	            WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (TARGET_YEAR = '' + @TARGET_YEAR + '') AND (TARGET_MONTH = '' + @TARGET_MONTH + '');
            	            IF @@ROWCOUNT = 0
                            INSERT INTO TBL_BRANCH_TARGET_PLAN_TMP(BRANCH_ID, TARGET_YEAR, TARGET_AMOUNT, NOA, TARGET_AMOUNT_REAL, NOA_REAL, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE, TARGET_MONTH) 
                            VALUES(@BRANCH_ID, @TARGET_YEAR, @TARGET_AMOUNT, @NOA, @TARGET_AMOUNT_REAL, @NOA_REAL, @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE, @TARGET_MONTH)
                            ");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchTarget.BranchID);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, BranchTarget.TargetYear);
            db.AddInParameter(cmd, "@TARGET_AMOUNT", DbType.Int64, BranchTarget.TargetAmount);
            db.AddInParameter(cmd, "@NOA", DbType.Int32, BranchTarget.Noa);
            db.AddInParameter(cmd, "@TARGET_AMOUNT_REAL", DbType.Int64, BranchTarget.TargetAmountReal);
            db.AddInParameter(cmd, "@NOA_REAL", DbType.Int32, BranchTarget.NoaReal);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, BranchTarget.EntryBy);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, BranchTarget.EntryDate);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, BranchTarget.UpdateBy);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, BranchTarget.UpdateDate);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, BranchTarget.ActType);
            db.AddInParameter(cmd, "@TARGET_MONTH", DbType.String, BranchTarget.TARGET_MONTH);

            db.ExecuteNonQuery(cmd);
        }

        public void Approve(string BranchID, string TargetYear, string TargetMonth, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("BRANCH_TARGET_PLAN_APPROVAL");

            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchID);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            db.AddInParameter(cmd, "@TARGET_MONTH", DbType.String, TargetMonth);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

            db.ExecuteNonQuery(cmd);
        }

        public void Reject(string BranchID, string TargetYear, string TargetMonth)
        {
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();

            string sql = String.Format("DELETE FROM TBL_BRANCH_TARGET_PLAN_TMP WHERE (BRANCH_ID = '{0}') AND (TARGET_YEAR = '{1}' AND TARGET_MONTH = '{2}');", BranchID, TargetYear, TargetMonth);
            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.ExecuteNonQuery(cmd);
        }
	}
}
